import pandas as pd

# 读取表格1和表格2
df1 = pd.read_excel('extracted_texts.xlsx')
df2 = pd.read_excel('游戏分类_train.xlsx')



# 从第二行开始的数据
df1 = df1.iloc[0:]
df2 = df2.iloc[0:]

# 初始化列表来保存每列的计数结果
count1_list = []
count2_list = []
count3_list = []
count4_list = []
count5_list = []
count6_list = []
count7_list = []
count8_list = []
count9_list = []

# 遍历表格1的每一列
for col in df1.columns:
    # 初始化计数器
    count1 = 0
    count2 = 0
    count3 = 0
    count4 = 0
    count5 = 0
    count6 = 0    
    count7 = 0
    count8 = 0
    count9 = 0


    # 遍历表格1的当前列的每一项
    for index, value in df1[col].items():
        if value == 1:
            # 检查表格2的标签列对应单元格是否为1
            if df2.at[index, 'label'] == '地铁跑酷':
                count1 += 1
            elif df2.at[index, 'label'] == '蛋仔派对':
                count2 += 1
            elif df2.at[index, 'label'] == '金铲铲之战':
                count3 += 1
            elif df2.at[index, 'label'] == '英雄联盟':
                count4 += 1
            elif df2.at[index, 'label'] == '原神':
                count5 += 1
            elif df2.at[index, 'label'] == '和平精英':
                count6 += 1
            elif df2.at[index, 'label'] == '斗罗大陆':
                count7 += 1
            elif df2.at[index, 'label'] == '第五人格':
                count8 += 1
            elif df2.at[index, 'label'] == '王者荣耀':
                count9 += 1

    # 将计数结果添加到列表中
    count1_list.append(count1)
    count2_list.append(count2)
    count3_list.append(count3)
    count4_list.append(count4)
    count5_list.append(count5)
    count6_list.append(count6)
    count7_list.append(count7)
    count8_list.append(count8)
    count9_list.append(count9)


# 创建一个新的DataFrame来保存所有列的计数结果
result_df = pd.DataFrame({'地铁跑酷': count1_list, '蛋仔派对': count2_list, '金铲铲之战': count3_list,
                          '英雄联盟': count4_list, '原神': count5_list, '和平精英': count6_list,
                          '斗罗大陆': count7_list, '第五人格': count8_list, '王者荣耀': count9_list,})

# 转置result_df
result_df_transposed = result_df.transpose()

# 计算每列第2行和第3行的和
sum_of_rows = result_df_transposed.iloc[0:9].sum(axis=0)

# 创建一个新的DataFrame，包含求和结果
sum_df = pd.DataFrame([sum_of_rows])
sum_df_index = pd.Index(['总和'])
sum_df.index = sum_df_index
# 将新DataFrame添加到转置后的DataFrame中，不保留索引
result_df_transposed = pd.concat([result_df_transposed, sum_df])


# 将转置并去除第一列后的结果保存到新的Excel文件中
result_df_transposed.to_excel('summy_result.xlsx')